9  Condition Control

9.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

  • om
  • ex

9.2 Conditions

Quite often, you would like different things happen based on different values/when some logics return TRUE/FALSE. Each of these comparison/logic test is called a condition. After each condition, you need to specify what behaviour needs to happen.

Take a look at the following example, when we need to create a friendly label for different repayment terms for invoices.

CASE…WHEN…THEN…END

SELECT invoice_number, terms_id,
    CASE terms_id
        WHEN 1 THEN 'Net due 10 days'
        WHEN 2 THEN 'Net due 20 days'
        WHEN 3 THEN 'Net due 30 days'
        WHEN 4 THEN 'Net due 60 days'
        WHEN 5 THEN 'Net due 90 days'
    END AS terms
FROM invoices
Displaying records 1 - 15
invoice_number terms_id terms
989319-457 3 Net due 30 days
263253241 3 Net due 30 days
963253234 3 Net due 30 days
2-000-2993 3 Net due 30 days
963253251 3 Net due 30 days
963253261 3 Net due 30 days
963253237 3 Net due 30 days
125520-1 1 Net due 10 days
97/488 3 Net due 30 days
263253250 3 Net due 30 days
963253262 3 Net due 30 days
I77271-O01 2 Net due 20 days
111-92R-10096 2 Net due 20 days
25022117 4 Net due 60 days
P02-88D77S7 3 Net due 30 days

Another example, we will create a custom payment_owned column by harnessing information from different types of payment statuses among the vendors:

SELECT invoice_id,
  vendor_id,
  invoice_due_date,
  payment_date,
  invoice_total,
  payment_total,
  credit_total,
  CASE  
      WHEN payment_date > invoice_due_date THEN invoice_total - payment_total - credit_total
      WHEN invoice_total - payment_total - credit_total = 0 THEN payment_total
      WHEN invoice_total - payment_total - credit_total < 0 THEN invoice_total - payment_total - credit_total
      ELSE 0
      END AS payment_owed
FROM invoices
Displaying records 1 - 15
invoice_id vendor_id invoice_due_date payment_date invoice_total payment_total credit_total payment_owed
1 122 2022-05-08 2022-05-07 3813.33 3813.33 0 3813.33
2 123 2022-05-10 2022-05-14 40.20 40.20 0 0.00
3 123 2022-05-13 2022-05-09 138.75 138.75 0 138.75
4 123 2022-05-16 2022-05-12 144.70 144.70 0 144.70
5 123 2022-05-16 2022-05-11 15.50 15.50 0 15.50
6 123 2022-05-16 2022-05-21 42.75 42.75 0 0.00
7 123 2022-05-21 2022-05-22 172.50 172.50 0 0.00
8 89 2022-05-04 2022-05-01 95.00 95.00 0 95.00
9 121 2022-05-24 2022-05-21 601.95 601.95 0 601.95
10 123 2022-05-24 2022-05-22 42.67 42.67 0 42.67
11 123 2022-05-25 2022-05-20 42.50 42.50 0 42.50
12 96 2022-05-16 2022-05-13 662.00 662.00 0 662.00
13 95 2022-05-20 2022-05-23 16.33 16.33 0 0.00
14 115 2022-06-10 2022-06-10 6.00 6.00 0 6.00
15 48 2022-06-02 2022-05-30 856.92 856.92 0 856.92

Last example, Case when statement can be directly embedded into summary query. This can make conditional summary query so much simpler without involving any Common Table Expressions or subqueries.

SELECT vendor_id,
  SUM(CASE  
      WHEN payment_date > invoice_due_date THEN invoice_total - payment_total - credit_total
      WHEN invoice_total - payment_total - credit_total = 0 THEN payment_total
      WHEN invoice_total - payment_total - credit_total < 0 THEN invoice_total - payment_total - credit_total
      ELSE 0
      END) AS total_payment_owed
FROM invoices
GROUP BY vendor_id
Displaying records 1 - 15
vendor_id total_payment_owed
34 0.00
37 116.00
48 856.92
72 21842.00
80 0.00
81 936.93
82 0.00
83 1575.00
86 2433.00
88 0.00
89 95.00
90 356.48
94 0.00
95 39.77
96 662.00

IF

This is quite similar to the Microsoft Excel IF function. IF is less versatile than CASE WHEN statement because it only examines 1 set of logics. However, IF is effective to write.

Here, we create flag column for vendor from City of Fresno:

SELECT vendor_name, 
  IF(vendor_city = 'Fresno', 'Yes', 'No') AS is_city_fresno
FROM vendors
Displaying records 1 - 15
vendor_name is_city_fresno
US Postal Service No
National Information Data Ctr No
Register of Copyrights No
Jobtrak No
Newbrige Book Clubs No
California Chamber Of Commerce No
Towne Advertiser’s Mailing Svcs No
BFI Industries Yes
Pacific Gas & Electric No
Robbins Mobile Lock And Key Yes
Bill Marvin Electric Inc Yes
City Of Fresno Yes
Golden Eagle Insurance Co No
Expedata Inc Yes
ASC Signs Yes

IFNULL

The following allows to replace NULL values if there are any in the field. Very useful before you use the field in WHERE or HAVING, because they exclude NULL values when comparing.

Example, we want to create a label for all vendors that haven’t paid us.

First, let’s see the ones without any payment date just yet:

SELECT *
FROM invoices
WHERE payment_date IS NULL
LIMIT 15
11 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
89 72 39104 2022-07-10 85.31 0 0 3 2022-08-09 NA
94 123 963253264 2022-07-18 52.25 0 0 3 2022-08-17 NA
98 83 31361833 2022-07-21 579.42 0 0 2 2022-08-10 NA
99 123 263253268 2022-07-21 59.97 0 0 3 2022-08-20 NA
100 123 263253270 2022-07-22 67.92 0 0 3 2022-08-21 NA
101 123 263253273 2022-07-22 30.75 0 0 3 2022-08-21 NA
102 110 P-0608 2022-07-23 20551.18 0 1200 3 2022-08-22 NA
105 106 9982771 2022-07-24 503.20 0 0 3 2022-08-23 NA
110 80 134116 2022-07-28 90.36 0 0 2 2022-08-17 NA
112 110 0-2436 2022-07-31 10976.06 0 0 3 2022-08-30 NA
113 37 547480102 2022-08-01 224.00 0 0 3 2022-08-31 NA

Let’s see the result of our SQL application.

SELECT payment_date,
       IFNULL(payment_date, 'No Payment') AS new_date
FROM invoices
ORDER BY payment_date
LIMIT 15
Displaying records 1 - 15
payment_date new_date
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
2022-05-01 2022-05-01
2022-05-07 2022-05-07
2022-05-09 2022-05-09
2022-05-11 2022-05-11

COALESCE

COALESCE(): Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

IFNULL vs COALESCE:

The main difference between the two is that IFNULL function takes two arguments and returns the first one if it’s not NULL or the second if the first one is NULL.

COALESCE function can take two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are null.

The following example has the same effect as IFNULL example above.

SELECT payment_date,
  COALESCE(payment_date, 'No Payment') AS new_date
FROM invoices
WHERE payment_date IS NULL
11 records
payment_date new_date
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment
NA No Payment

The following example fully demonstrate how COALESCE works when multiple conditions are passed in:

SELECT vendors.vendor_id,
  vendor_phone,
  vendor_name,
  vendor_state,
  invoice_id, 
  invoice_date,
  invoice_total,
  COALESCE(invoice_id, vendor_phone, invoice_total, 'No invoice', 'No vendor phone number','No invoice total') AS NEW_COL
FROM vendors LEFT JOIN invoices ON invoices.vendor_id = vendors.vendor_id
Displaying records 1 - 15
vendor_id vendor_phone vendor_name vendor_state invoice_id invoice_date invoice_total NEW_COL
1 (800) 555-1205 US Postal Service WI NA NA NA (800) 555-1205
2 (301) 555-8950 National Information Data Ctr DC NA NA NA (301) 555-8950
3 NA Register of Copyrights DC NA NA NA No invoice
4 (800) 555-8725 Jobtrak CA NA NA NA (800) 555-8725
5 (800) 555-9980 Newbrige Book Clubs NJ NA NA NA (800) 555-9980
6 (916) 555-6670 California Chamber Of Commerce CA NA NA NA (916) 555-6670
7 NA Towne Advertiser’s Mailing Svcs CA NA NA NA No invoice
8 (559) 555-1551 BFI Industries CA NA NA NA (559) 555-1551
9 (800) 555-6081 Pacific Gas & Electric CA NA NA NA (800) 555-6081
10 (559) 555-9375 Robbins Mobile Lock And Key CA NA NA NA (559) 555-9375
11 (559) 555-5106 Bill Marvin Electric Inc CA NA NA NA (559) 555-5106
12 (559) 555-9999 City Of Fresno CA NA NA NA (559) 555-9999
13 NA Golden Eagle Insurance Co CA NA NA NA No invoice
14 (559) 555-9586 Expedata Inc CA NA NA NA (559) 555-9586
15 NA ASC Signs CA NA NA NA No invoice